#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
质量控制部组织架构Excel自动生成工具
运行此脚本将在当前目录生成"质量控制部组织架构.xlsx"文件
"""

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

def create_qc_organization_chart():
    """创建质量控制部组织架构Excel文件"""
    
    # 创建工作簿和工作表
    wb = Workbook()
    ws = wb.active
    ws.title = "质量控制部组织架构"
    
    # 设置列宽
    column_widths = {
        'A': 30,   # 职位/姓名列
        'B': 45,   # 工作内容列
        'C': 15,   # 上班时间列
        'D': 15,   # 下班时间列
        'E': 20    # 备注列
    }
    for col, width in column_widths.items():
        ws.column_dimensions[col].width = width
    
    # 定义样式
    title_font = Font(size=18, bold=True, color="FFFFFF")
    subtitle_font = Font(size=14, bold=True, color="FFFFFF")
    header_font = Font(size=12, bold=True)
    normal_font = Font(size=11)
    
    title_fill = PatternFill(start_color="003366", end_color="003366", fill_type="solid")
    department_fill = PatternFill(start_color="0059B3", end_color="0059B3", fill_type="solid")
    team_fill = PatternFill(start_color="337AB7", end_color="337AB7", fill_type="solid")
    header_fill = PatternFill(start_color="D9E2F3", end_color="D9E2F3", fill_type="solid")
    member_fill = PatternFill(start_color="E7E6E6", end_color="E7E6E6", fill_type="solid")
    
    # 边框样式
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    
    # 居中对齐
    center_alignment = Alignment(horizontal='center', vertical='center')
    left_alignment = Alignment(horizontal='left', vertical='center')
    
    # 合并单元格并设置标题
    ws.merge_cells('A1:E1')
    ws['A1'] = "质量控制部组织架构"
    ws['A1'].font = title_font
    ws['A1'].fill = title_fill
    ws['A1'].alignment = center_alignment
    
    # 设置副标题
    ws.merge_cells('A2:E2')
    ws['A2'] = "部门 / 团队 / 成员"
    ws['A2'].font = subtitle_font
    ws['A2'].fill = department_fill
    ws['A2'].alignment = center_alignment
    
    # 设置表头
    headers = ["职位/姓名", "工作内容", "上班时间", "下班时间", "备注"]
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=3, column=col)
        cell.value = header
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = center_alignment
        cell.border = thin_border
    
    # 组织架构数据
    organization_data = [
        # 部门层级
        {"level": "department", "name": "质量控制部总监", "work": "全面负责质量控制部工作，制定质量战略和政策", "start": "09:00", "end": "18:00", "note": "直接向CEO汇报"},
        
        # 团队层级
        {"level": "team", "name": "质量检验团队", "work": "负责产品入库检验、过程检验和最终检验", "start": "08:30", "end": "17:30", "note": "团队共8人"},
        {"level": "member", "name": "检验组长 - 张丽华", "work": "负责检验团队日常管理，制定检验计划", "start": "08:30", "end": "17:30", "note": "5年经验"},
        {"level": "member", "name": "高级检验员 - 李明", "work": "负责关键工序检验，培训新员工", "start": "08:30", "end": "17:30", "note": "3年经验"},
        {"level": "member", "name": "检验员 - 王小红", "work": "负责日常产品检验，记录检验数据", "start": "08:30", "end": "17:30", "note": "1年经验"},
        
        # 团队层级
        {"level": "team", "name": "质量保证团队", "work": "负责质量体系维护、审核和持续改进", "start": "09:00", "end": "18:00", "note": "团队共6人"},
        {"level": "member", "name": "QA经理 - 陈志强", "work": "负责质量体系建设和维护，组织内审", "start": "09:00", "end": "18:00", "note": "ISO9001内审员"},
        {"level": "member", "name": "质量工程师 - 刘芳", "work": "负责质量数据分析和改进项目推进", "start": "09:00", "end": "18:00", "note": "六西格玛绿带"},
        
        # 团队层级
        {"level": "team", "name": "实验室团队", "work": "负责产品性能测试、可靠性测试和材料分析", "start": "08:00", "end": "17:00", "note": "团队共4人"},
        {"level": "member", "name": "实验室主管 - 赵敏", "work": "负责实验室管理和测试方法开发", "start": "08:00", "end": "17:00", "note": "化学专业硕士"},
        {"level": "member", "name": "测试工程师 - 孙伟", "work": "负责产品可靠性测试和环境测试", "start": "08:00", "end": "17:00", "note": "电子工程专业"}
    ]
    
    # 填充数据
    row = 4
    for data in organization_data:
        # 根据层级设置样式
        if data["level"] == "department":
            fill = department_fill
            font = Font(size=12, bold=True, color="FFFFFF")
        elif data["level"] == "team":
            fill = team_fill
            font = Font(size=11, bold=True, color="FFFFFF")
        else:  # member
            fill = member_fill
            font = normal_font
        
        # 填充数据
        ws.cell(row=row, column=1, value=data["name"]).font = font
        ws.cell(row=row, column=1).fill = fill
        ws.cell(row=row, column=1).alignment = left_alignment
        ws.cell(row=row, column=1).border = thin_border
        
        ws.cell(row=row, column=2, value=data["work"]).font = normal_font
        ws.cell(row=row, column=2).alignment = left_alignment
        ws.cell(row=row, column=2).border = thin_border
        
        ws.cell(row=row, column=3, value=data["start"]).font = normal_font
        ws.cell(row=row, column=3).alignment = center_alignment
        ws.cell(row=row, column=3).border = thin_border
        
        ws.cell(row=row, column=4, value=data["end"]).font = normal_font
        ws.cell(row=row, column=4).alignment = center_alignment
        ws.cell(row=row, column=4).border = thin_border
        
        ws.cell(row=row, column=5, value=data["note"]).font = normal_font
        ws.cell(row=row, column=5).alignment = left_alignment
        ws.cell(row=row, column=5).border = thin_border
        
        row += 1
    
    # 设置行高
    ws.row_dimensions[1].height = 35
    ws.row_dimensions[2].height = 25
    ws.row_dimensions[3].height = 25
    
    # 保存文件
    filename = "质量控制部组织架构.xlsx"
    wb.save(filename)
    print(f"✅ 成功生成文件：{filename}")
    print(f"📊 包含 {len(organization_data)} 条组织架构数据")
    print("📝 您现在可以打开文件进行编辑和补充")

if __name__ == "__main__":
    create_qc_organization_chart()
